/**
 * Copyright (c) 2020 Coder League
 * All rights reserved.
 *
 * File：CybercafeDaoImpl.java
 * History:
 *         2020年12月30日: Initially created, Chrise.
 */
package club.coderleague.cfss.dao.impl;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import club.coderleague.cfss.dao.extension.CybercafeDaoExtension;
import club.coderleague.cfss.domain.beans.GoodSearcher;
import club.coderleague.cfss.domain.entities.Cybercafes;
import club.coderleague.cfss.domain.enums.EntityState;
import club.coderleague.cfss.util.CommonUtil;
import club.coderleague.data.jpa.domain.Page;
import club.coderleague.data.jpa.id.support.SnowflakeV4;
import club.coderleague.data.jpa.repository.support.AbstractDataRepositoryExtension;

/**
 * 网吧数据访问对象实现。
 * @author Chrise
 */
public class CybercafeDaoImpl extends AbstractDataRepositoryExtension<Cybercafes, Long, SnowflakeV4> implements CybercafeDaoExtension {
	/**
	 * @see club.coderleague.cfss.dao.extension.CybercafeDaoExtension#existsCybercafe(java.lang.String, java.lang.String, java.lang.Long)
	 */
	@Override
	public boolean existsCybercafe(String entname, String buslicenseno, Long entityid) {
		String sql = "select count(entityid) from cybercafes where entitystate in (:entitystate) "
			+ "and (entname = :entname or buslicenseno = :buslicenseno)";
		Map<String, Object> params = new HashMap<>();
		params.put("entitystate", Arrays.asList(EntityState.APPLY.getValue(), EntityState.AUDITING.getValue(), EntityState.OPENING.getValue()));
		params.put("entname", entname);
		params.put("buslicenseno", buslicenseno);
		if (entityid != null) {
			sql += " and entityid != :entityid";
			params.put("entityid", entityid);
		}
		
		return (super.queryLongBySql(sql, params) > 0);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.CybercafeDaoExtension#queryCybercafePageData(java.util.List, java.lang.String, java.lang.String, java.lang.Integer, java.lang.Integer)
	 */
	@Override
	public Page<Map<String, Object>> queryCybercafePageData(List<Integer> states, String keyword, String buslicense, Integer pageIndex, Integer pageSize) {
		Map<String, Object> params = new HashMap<>();
		
		String csql = "select count(c.entityid) from Cybercafes c " 
			+ "left join Areas p on c.provinceid = p.entityid " 
			+ "left join Areas i on c.cityid = i.entityid " 
			+ "left join Areas d on c.districtid = d.entityid " 
			+ "left join Areas s on c.streetid = s.entityid " 
			+ "left join Users u on c.auditor = u.entityid where c.entitystate in (:entitystate)";
		String qsql = "select c.entityid, c.entname, c.buslicenseno, concat('" + buslicense + "', c.buslicense) as buslicense, c.leader, c.contactnumber, " 
			+ "p.areaname as province, i.areaname as city, d.areaname as district, s.areaname as street, c.detailaddr, " 
			+ "c.applytime, u.username as auditor, c.audittime, c.entitystate from Cybercafes c " 
			+ "left join Areas p on c.provinceid = p.entityid " 
			+ "left join Areas i on c.cityid = i.entityid " 
			+ "left join Areas d on c.districtid = d.entityid " 
			+ "left join Areas s on c.streetid = s.entityid " 
			+ "left join Users u on c.auditor = u.entityid where c.entitystate in (:entitystate)";
		params.put("entitystate", states);
		
		if (CommonUtil.isNotEmpty(keyword)) {
			csql += " and (c.entname like :keyword or c.buslicenseno like :keyword or c.leader like :keyword or c.contactnumber like :keyword)";
			qsql += " and (c.entname like :keyword or c.buslicenseno like :keyword or c.leader like :keyword or c.contactnumber like :keyword)";
			params.put("keyword", "%" + keyword + "%");
		}
		
		qsql += " order by c.entitystate";
		
		return super.queryMapPageBySql(csql, qsql, pageIndex, pageSize, params);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.CybercafeDaoExtension#queryCybercafeDetail(long, java.lang.String)
	 */
	@Override
	public Map<String, Object> queryCybercafeDetail(long entityid, String buslicense) {
		String sql = "select c.entname, c.buslicenseno, concat('" + buslicense + "', c.buslicense) as buslicense, c.leader, c.contactnumber, " 
			+ "concat(p.areaname, ' ', i.areaname, ' ', d.areaname, ' ', s.areaname) as areaname, c.detailaddr from Cybercafes c " 
			+ "left join Areas p on c.provinceid = p.entityid " 
			+ "left join Areas i on c.cityid = i.entityid " 
			+ "left join Areas d on c.districtid = d.entityid " 
			+ "left join Areas s on c.streetid = s.entityid where c.entityid = ?0";
		return super.queryMapBySql(sql, entityid);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.CybercafeDaoExtension#queryCybercafeState(long)
	 */
	@Override
	public Integer queryCybercafeState(long cybercafe) {
		String jpql = "select entitystate from Cybercafes where entityid = ?0";
		return super.queryInteger(jpql, cybercafe);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.CybercafeDaoExtension#queryCybercafeArea(long)
	 */
	@Override
	public GoodSearcher queryCybercafeArea(long cybercafe) {
		String sql = "select provinceid, cityid, districtid, streetid from Cybercafes where entityid = ?0";
		return super.queryCustomBeanBySql(GoodSearcher.class, sql, cybercafe);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.CybercafeDaoExtension#queryPersonalInfo(long)
	 */
	@Override
	public Map<String, Object> queryPersonalInfo(long cybercafe) {
		String sql = "select c.entitystate from Cybercafes c where c.entityid = ?0";
		return super.queryMapBySql(sql, cybercafe);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.CybercafeDaoExtension#queryCybercafeData(long, java.lang.String)
	 */
	@Override
	public Map<String, Object> queryCybercafeData(long cybercafe, String buslicense) {
		String sql = "select c.entname, c.buslicenseno, concat('" + buslicense + "', c.buslicense) as buslicense, c.leader, c.contactnumber, " 
			+ "c.provinceid, pn.areaname as pareaname, c.cityid, cn.areaname as careaname, c.districtid, dn.areaname as dareaname, c.streetid, sn.areaname as sareaname, " 
			+ "c.detailaddr from Cybercafes c, Areas pn, Areas cn, Areas dn, Areas sn " 
			+ "where c.provinceid = pn.entityid and c.cityid = cn.entityid and c.districtid = dn.entityid and c.streetid = sn.entityid and c.entityid = ?0";
		return super.queryMapBySql(sql, cybercafe);
	}
}
